﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using KPIS.DBM; 
using KPIS.GERP.GHRMS.IDAL;
using KPIS.GERP.GHRMS.MODEL;

namespace KPIS.GERP.GHRMS.DAL
{
	public class HisHealthCheckDAL  : IHisHealthCheckDAL
	{
		private readonly IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

		private Dictionary<string, object> Db2Cs(DataRow dataRow)
		{
			var keyValuePair = new Dictionary<string, object>();
			foreach (DataColumn dataColumn in dataRow.Table.Columns)
			{
				string columnName = dataColumn.ColumnName;
				object value = dataRow[dataColumn.ColumnName];
				keyValuePair.Add(columnName, value == null ?  null : (value.ToString().Length == 0 ? null : value)); 
			}
			return keyValuePair;
		}

		public IList<HisHealthCheckInfo> List(
			int? sequence = null,
			int? empSeq = null,
			int? year = null,
			HisHealthCheckInfo.RoundTypeOption roundType = HisHealthCheckInfo.RoundTypeOption.Yearly,
			int? roundNumber = null)
		{
			var listHisHealthCheckInfo = new List<HisHealthCheckInfo>();
            
			try
			{
				dbManager.Open();
				dbManager.CreateParameters(6);
				int i = 0;
				dbManager.AddParameters(i++, "@record_status", SystemInfo._ActiveRecordStatus);
				dbManager.AddParameters(i++, "@health_check_seq", sequence);
				dbManager.AddParameters(i++, "@emp_seq", empSeq);
				dbManager.AddParameters(i++, "@round_year", year);
				dbManager.AddParameters(i++, "@round_type", roundType.ToString());
				dbManager.AddParameters(i++, "@round_number", roundNumber);

				var stbSQL = new StringBuilder(@"
                    SELECT 	
                    health_check_seq, 
                    emp_seq, 
                    round_type, 
                    round_number, 
                    round_date, 
                    round_year, 
                    body_weight, 
                    body_height, 
                    health_Glocox, 
                    health_Cric_Acid, 
                    health_Alk, 
                    health_AST_or_S6OT, 
                    health_ALT_or_S6PT, 
                    health_Cholestoral, 
                    health_HDL, 
                    health_LDL, 
                    health_T6, 
                    health_HCT, 
                    health_PIT, 
                    health_CXK, 
                    health_CK6, 
                    health_BUNCr, 
                    remark, 
                    created_by, 
                    created_when, 
                    updated_by, 
                    updated_when, 
                    record_status
	 
                    FROM 
                    hrm_ps_emp_health_check

                    WHERE 
                    record_status = @record_status
                    ");

				if (sequence != null)
					stbSQL.Append(" and health_check_seq = @health_check_seq" + Environment.NewLine);

				if (empSeq != null)
					stbSQL.Append(" and emp_seq = @emp_seq" + Environment.NewLine);

				if (year != null)
					stbSQL.Append(" and round_year = @round_year" + Environment.NewLine);

				if (roundNumber != null)
					stbSQL.Append(" and round_number = @round_number" + Environment.NewLine);

				stbSQL.Append(" and round_type = @round_type" + Environment.NewLine);

				var strSql = stbSQL.ToString();
				DataSet dataSet = dbManager.ExecuteDataSet(CommandType.Text, strSql);

				if (dataSet != null && dataSet.Tables.Count > 0)

					foreach (DataRow dataRow in dataSet.Tables[0].Rows)
					{
						Dictionary<string, object> dataList = Db2Cs(dataRow);

						listHisHealthCheckInfo.Add(
							new HisHealthCheckInfo
								{
									HealthCheckSequence = ConvertNullInt(dataList["health_check_seq"]),
									EmpSeq = Convert.ToInt32(dataList["emp_seq"]),
									RoundType = Convert.ToString(dataList["round_type"]),
									RoundNumber = ConvertNullInt(dataList["round_number"]),
									RoundDate = Convert.ToDateTime(dataList["round_date"]),
									RoundYear = Convert.ToInt32(dataList["round_year"]),
									BodyWeight = ConvertNullDecimal(dataList["body_weight"]),
									BodyHeight = ConvertNullDecimal( dataList["body_height"]),
									HealthGlocox = ConvertNullDecimal( dataList["health_Glocox"]),
									HealthCricAcid = ConvertNullDecimal( dataList["health_Cric_Acid"]),
									HealthAlk = ConvertNullDecimal( dataList["health_Alk"]),
									HealthAstOrS6Ot = ConvertNullDecimal( dataList["health_AST_or_S6OT"]),
									HealthAltOrS6Pt = ConvertNullDecimal( dataList["health_ALT_or_S6PT"]),
									HealthCholestoral = ConvertNullDecimal( dataList["health_Cholestoral"]),
									HealthHdl = ConvertNullDecimal( dataList["health_HDL"]),
									HealthLdl = ConvertNullDecimal( dataList["health_LDL"]),
									HealthT6 = ConvertNullDecimal( dataList["health_T6"]),
									HealthHct = ConvertNullDecimal( dataList["health_HCT"]),
									HealthPit = ConvertNullDecimal( dataList["health_PIT"]),
									HealthCxk = Convert.ToBoolean( dataList["health_CXK"]),
                                    HealthCk6 = Convert.ToBoolean(dataList["health_CK6"]),
                                    HealthBunCr = ConvertNullDecimal(dataList["health_BUNCr"])?? 0,
									Remark = Convert.ToString(dataList["remark"]),
									CreatedBy = ConvertNullInt(dataList["created_by"]),
									CreatedWhen = ConvertNullDateTime(dataList["created_when"]),
									UpdatedBy = ConvertNullInt(dataList["updated_by"]),
									UpdatedWhen = ConvertNullDateTime(dataList["updated_when"]),
									RecordStatus = Convert.ToString( dataList["record_status"])
								});
					}
			}
			catch (Exception exception)
            {
                throw exception;
			}
			finally
			{
				dbManager.CloseReader();
				dbManager.Dispose();
			}

			return listHisHealthCheckInfo;

		}


		public Nullable<int> ConvertNullInt(Object o)
		{
			Nullable<int> result = (o == null ? null : (o.ToString().Length == 0 ? null : (Nullable<int>)Convert.ToInt32(o)));
			return result;
		}

		public Nullable<double> ConvertNullDouble(Object o)
		{
			Nullable<double> result = (o == null ? null : (o.ToString().Length == 0 ? null : (Nullable<double>)Convert.ToDouble(o)));
			return result;
		}

		public Nullable<decimal> ConvertNullDecimal(Object o)
		{
			Nullable<decimal> result = (o == null ? null : (o.ToString().Length == 0 ? null : (Nullable<decimal>)Convert.ToDecimal(o)));
			return result;
		}

		public Nullable<DateTime> ConvertNullDateTime(Object o)
		{
			Nullable<DateTime> result = (o == null ? null : (o.ToString().Length == 0 ? null : (Nullable<DateTime>)Convert.ToDateTime(o)));
			return result;
		}


		public void Insert(HisHealthCheckInfo info)
		{
			try
			{
				dbManager.Open();
				dbManager.CreateParameters(27);

				int i = 0;
				dbManager.AddParameters(i++, "@emp_seq", info.EmpSeq);
				dbManager.AddParameters(i++, "@round_type", info.RoundType);
				dbManager.AddParameters(i++, "@round_number", info.RoundNumber);
				dbManager.AddParameters(i++, "@round_date", info.RoundDate);
				dbManager.AddParameters(i++, "@round_year", info.RoundYear);

				dbManager.AddParameters(i++, "@body_weight", info.BodyWeight);
				dbManager.AddParameters(i++, "@body_height", info.BodyHeight);
				dbManager.AddParameters(i++, "@health_Glocox", info.HealthGlocox);
				dbManager.AddParameters(i++, "@health_Cric_Acid", info.HealthCricAcid);
				dbManager.AddParameters(i++, "@health_Alk", info.HealthAlk);

				dbManager.AddParameters(i++, "@health_AST_or_S6OT", info.HealthAltOrS6Pt);
				dbManager.AddParameters(i++, "@health_ALT_or_S6PT", info.HealthAltOrS6Pt);
				dbManager.AddParameters(i++, "@health_Cholestoral", info.HealthCholestoral);
				dbManager.AddParameters(i++, "@health_HDL", info.HealthHdl);
				dbManager.AddParameters(i++, "@health_LDL", info.HealthLdl);

				dbManager.AddParameters(i++, "@health_T6", info.HealthT6);
				dbManager.AddParameters(i++, "@health_HCT", info.HealthHct);
				dbManager.AddParameters(i++, "@health_PIT", info.HealthPit);
				dbManager.AddParameters(i++, "@health_CXK", info.HealthCxk);
				dbManager.AddParameters(i++, "@health_CK6", info.HealthCk6);

				dbManager.AddParameters(i++, "@remark", info.Remark);
				dbManager.AddParameters(i++, "@created_by", info.CreatedBy);
				dbManager.AddParameters(i++, "@created_when", info.CreatedWhen);
				dbManager.AddParameters(i++, "@updated_by", info.UpdatedBy);
				dbManager.AddParameters(i++, "@updated_when", info.UpdatedWhen);

                dbManager.AddParameters(i++, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(i++, "@health_BUNCr", info.HealthBunCr??0);

				string stbSQL = @"
INSERT INTO hrm_ps_emp_health_check 
	(emp_seq, 
	round_type, 
	round_number, 
	round_date, 
	round_year, 

	body_weight, 
	body_height, 
	health_Glocox, 
	health_Cric_Acid, 
	health_Alk, 

	health_AST_or_S6OT, 
	health_ALT_or_S6PT, 
	health_Cholestoral, 
	health_HDL, 
	health_LDL, 

	health_T6, 
	health_HCT, 
	health_PIT, 
	health_CXK, 
	health_CK6, 

	remark, 
	created_by, 
	created_when, 
	updated_by, 
	updated_when, 

	record_status,
	health_BUNCr
	)
	VALUES
	(@emp_seq, 
	@round_type, 
	@round_number, 
	@round_date, 
	@round_year, 

	@body_weight, 
	@body_height, 
	@health_Glocox, 
	@health_Cric_Acid, 
	@health_Alk, 

	@health_AST_or_S6OT, 
	@health_ALT_or_S6PT, 
	@health_Cholestoral, 
	@health_HDL, 
	@health_LDL, 

	@health_T6, 
	@health_HCT, 
	@health_PIT, 
	@health_CXK, 
	@health_CK6, 

	@remark, 
	@created_by, 
	@created_when, 
	@updated_by, 
	@updated_when, 

	@record_status,
	@health_BUNCr
	);";
				dbManager.ExecuteNonQuery(CommandType.Text, stbSQL);
			}
			catch (Exception exception)
			{
			    throw exception;
			}
			finally
			{
				dbManager.CloseReader();
				dbManager.Dispose();
			}
		}

		public void Update(HisHealthCheckInfo info)
		{
			//MakeLogText(new List<string> { "Update " + info.EmpSeq });
			try
			{
				dbManager.Open();
				dbManager.CreateParameters(26);

				int i = 0;
				dbManager.AddParameters(i++, "@emp_seq", info.EmpSeq);
				dbManager.AddParameters(i++, "@round_type", info.RoundType);
				dbManager.AddParameters(i++, "@round_number", info.RoundNumber);
				dbManager.AddParameters(i++, "@round_date", info.RoundDate);
				dbManager.AddParameters(i++, "@round_year", info.RoundYear);

				dbManager.AddParameters(i++, "@body_weight", info.BodyWeight);
				dbManager.AddParameters(i++, "@body_height", info.BodyHeight);
				dbManager.AddParameters(i++, "@health_Glocox", info.HealthGlocox);
				dbManager.AddParameters(i++, "@health_Cric_Acid", info.HealthCricAcid);
				dbManager.AddParameters(i++, "@health_Alk", info.HealthAlk);

				dbManager.AddParameters(i++, "@health_AST_or_S6OT", info.HealthAltOrS6Pt);
				dbManager.AddParameters(i++, "@health_ALT_or_S6PT", info.HealthAltOrS6Pt);
				dbManager.AddParameters(i++, "@health_Cholestoral", info.HealthCholestoral);
				dbManager.AddParameters(i++, "@health_HDL", info.HealthHdl);
				dbManager.AddParameters(i++, "@health_LDL", info.HealthLdl);

				dbManager.AddParameters(i++, "@health_T6", info.HealthT6);
				dbManager.AddParameters(i++, "@health_HCT", info.HealthHct);
				dbManager.AddParameters(i++, "@health_PIT", info.HealthPit);
				dbManager.AddParameters(i++, "@health_CXK", info.HealthCxk);
				dbManager.AddParameters(i++, "@health_CK6", info.HealthCk6);

				dbManager.AddParameters(i++, "@remark", info.Remark);
				dbManager.AddParameters(i++, "@updated_by", info.UpdatedBy);
				dbManager.AddParameters(i++, "@updated_when", info.UpdatedWhen);
				dbManager.AddParameters(i++, "@record_status", SystemInfo._ActiveRecordStatus);
				dbManager.AddParameters(i++, "@health_check_seq", info.HealthCheckSequence);

                dbManager.AddParameters(i++, "@health_BUNCr", info.HealthBunCr ??0 );

				string stbSQL = @"
UPDATE hrm_ps_emp_health_check 
	SET
	emp_seq = @emp_seq, 
	round_type = @round_type, 
	round_number = @round_number, 
	round_date = @round_date, 
	round_year = @round_year, 
	body_weight = @body_weight, 
	body_height = @body_height, 
	health_Glocox = @health_Glocox, 
	health_Cric_Acid = @health_Cric_Acid, 
	health_Alk = @health_Alk, 
	health_AST_or_S6OT = @health_AST_or_S6OT, 
	health_ALT_or_S6PT = @health_ALT_or_S6PT, 
	health_Cholestoral = @health_Cholestoral, 
	health_HDL = @health_HDL, 
	health_LDL = @health_LDL, 
	health_T6 = @health_T6, 
	health_HCT = @health_HCT, 
	health_PIT = @health_PIT, 
	health_CXK = @health_CXK, 
	health_CK6 = @health_CK6, 
	health_BunCr = @health_BUNCr, 
	remark = @remark, 
	updated_by = @updated_by, 
	updated_when = @updated_when, 
	record_status = @record_status 
	
	WHERE
	health_check_seq = @health_check_seq ;";

				dbManager.ExecuteNonQuery(CommandType.Text, stbSQL);
			}
			catch (Exception exception)
            {
                throw exception;
			}
			finally
			{
				dbManager.CloseReader();
				dbManager.Dispose();
			}
		}

		public void Delete(int sequence, int updatedBy)
		{
			try
			{
				dbManager.Open();
				dbManager.CreateParameters(2);

				int i = 0;
				dbManager.AddParameters(i++, "@record_status", SystemInfo._DeleteRecordStatus);
				dbManager.AddParameters(i++, "@health_check_seq", sequence);

				string stbSQL = @"
UPDATE hrm_ps_emp_health_check 
	SET
	record_status = @record_status 
	
	WHERE
	health_check_seq = @health_check_seq ;";

				dbManager.ExecuteNonQuery(CommandType.Text, stbSQL);
			}
			catch (Exception exception)
			{

			}
			finally
			{
				dbManager.CloseReader();
				dbManager.Dispose();
			}
		}
	}
}
